package com.teamwork.ZuccEcho.Repository;

import com.teamwork.ZuccEcho.Entity.Course;
import com.teamwork.ZuccEcho.Entity.Teacher;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public interface ClassApiRepository extends JpaRepository<Course,Long>, JpaSpecificationExecutor<Course> {
    Course findById(long id);

    @Query(value="select * from course where tch_id = ?1", nativeQuery = true)
    List<Course> findCoursesByTeacher(String tch_id);

    @Query(value="select * from course where c_id=(select c_id from course_student where stu_id = ?1)", nativeQuery = true)
    List<Course> getCoursesByStu(String stu_id);

    @Query(value="select count(*) from teacher where tch_id = ?1", nativeQuery = true)
    int findTeacher(String tch_id);

    @Transactional
    @Modifying
    @Query(value = "update course set c_status='已结课',c_closedate=?1 where c_id=?2",nativeQuery = true)
    void closeCourse(Date date, long c_id);

    @Transactional
    @Modifying
    @Query(value = "update course set c_status='已注销',c_canceldate=?1 where c_id=?2",nativeQuery = true)
    void cancelCourse(Date date, long c_id);

    @Query(value = "select count(*) from course_student where c_id=?1 && stu_id=?2",nativeQuery = true)
    int countStudent(long c_id,String stu_id);

    @Transactional
    @Modifying
    @Query(value = "insert into course_student(c_id,stu_id) values(?1,?2)",nativeQuery = true)
    void addStudent(long c_id,String stu_id);

    @Transactional
    @Modifying
    @Query(value = "delete from course_student where c_id=?1 && stu_id=?2",nativeQuery = true)
    void removeStudent(long c_id,String stu_id);
}
